﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace AcessaDadosBD.Repositorios
{
    public class ProfissionalRepository : IRepository<Profissional>
    {
        private IDbCommand Command { get; set; }

        public ProfissionalRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        public Profissional GetById(int id)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idPrestador, siglaConselho, numeroConselho, ufConselho, CPF, CBOS, judiciario FROM tbProfissional WHERE idPrestador=" + id;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    Profissional profissional = new Profissional()
                    {
                        ID = int.Parse(reader["idPrestador"].ToString()),
                        SiglaConselho = reader["siglaConselho"].ToString(),
                        NumeroConselho = reader["numeroConselho"].ToString(),
                        UFConselho = reader["ufConselho"].ToString(),
                        CPF = reader["CPF"].ToString(),
                        CBOS = reader["CBOS"].ToString(),
                        Judiciario = int.Parse(reader["judiciario"].ToString())
                    };
                    return profissional;
                }
                else
                {
                    throw new Exception("Profissional não encontrado");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public IQueryable<Profissional> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idPrestador, siglaConselho, numeroConselho, ufConselho, CPF, CBOS, judiciario FROM tbProfissional";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<Profissional> list = new List<Profissional>();
                while (reader.Read())
                {
                    Profissional profissional = new Profissional()
                    {
                        ID = int.Parse(reader["idPrestador"].ToString()),
                        SiglaConselho = reader["siglaConselho"].ToString(),
                        NumeroConselho = reader["numeroConselho"].ToString(),
                        UFConselho = reader["ufConselho"].ToString(),
                        CPF = reader["CPF"].ToString(),
                        CBOS = reader["CBOS"].ToString(),
                        Judiciario = int.Parse(reader["judiciario"].ToString())
                    };
                    list.Add(profissional);
                }
                return list.AsQueryable<Profissional>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public int Insert(Profissional entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = string.Format("INSERT INTO tbProfissional (idPrestador, siglaConselho, numeroConselho, ufConselho, CPF, CBOS, judiciario) VALUES "+
                                                    "({0},'{1}','{2}','{3}','{4}','{5}',{6})", entity.ID, entity.SiglaConselho, entity.NumeroConselho, entity.UFConselho, entity.CPF, entity.CBOS, entity.Judiciario);

                //Command.CommandText = "INSERT INTO tbProfissional (idPrestador, siglaConselho, numeroConselho, ufConselho, CPF, CBOS, judiciario) VALUES (" +
                //    entity.ID + ",'" + entity.SiglaConselho + "', '" + entity.NumeroConselho + ",'" + entity.UFConselho + ",'" + entity.CPF + ",'" + entity.CBOS +
                //    "," + entity.Judiciario + ")";
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();

                return entity.ID;
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Delete(Profissional entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbProfissional WHERE idPrestador=" + entity.ID;
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Update(Profissional entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "UPDATE tbProfissional SET siglaConselho='" + entity.SiglaConselho + "', numeroConselho='" + entity.NumeroConselho +
                    "', ufConselho='" + entity.UFConselho + "', CPF='" + entity.CPF + "', CBOS='" + entity.CBOS + "', judiciario=" + entity.Judiciario + 
                    " WHERE idPrestador=" + entity.ID;
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
    }
}
